﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using SharpObjects.Configuration;
using SharpObjects.Configuration.ConfigurationProviders;
using SharpObjects.Configuration.Utils;
using SharpObjects.Instantiation.Services;
using SharpObjects.Utils;

namespace SharpObjects.Data.Test
{
    class Program
    {
        static void Main(string[] args)
        {
            IConfigurationProvider config = new XmlConfigurationProvider("DbConfigSample.xml");
            ServiceFactory.Instance = config.GetObject<IServiceFactory>("//services");

            {
                using (IDb db = config.GetObject<IDb>("//databases/database_descriptor[@id='TestDatabase1']"))
                {

                }
            }

            {
                using (IDb db = ServiceFactory.Instance.GetService<IDb>("sql_server"))
                {

                }
            }

            Test0();
            Test1();
            Test2();
        }


        static void Test0()
        {

            /*
              
 
             CREATE TABLE [dbo].[test_status](
                [id] [int] IDENTITY(1,1) NOT NULL,
                [status] [nvarchar](50) NOT NULL
            ) ON [PRIMARY]


            SQL SERVER:             
             
            CREATE PROCEDURE [dbo].[SP_GET_STATUS] (@ID INT, @STATUS NVARCHAR OUT)
            AS
            BEGIN
                SELECT STATUS FROM TEST_STATUS WHERE ID = @ID
            END


             
             
            CREATE PROCEDURE [dbo].[SP_GET_STATUSES] 
            AS
            BEGIN
	            SELECT STATUS FROM TEST_STATUS
            END
  
             
            
            CREATE PROCEDURE [dbo].[SP_GET_CONSTANT] (@CONSTANT INT OUT)
            AS
            BEGIN
	            SELECT @CONSTANT = 5
            END
            
   			CREATE PROCEDURE [dbo].[GetData] 
				@min int , 
				@max int 
			AS
			BEGIN
				SELECT * from test_table1 where numeric_data >= @min and numeric_data <= @max
			END

              
            ORACLE:

            CREATE OR REPLACE PROCEDURE "GET_STATUSES" (
                cur_STATUSES OUT SYS_REFCURSOR
            )            
            
             
            CREATE OR REPLACE PROCEDURE "GetData" (
				nMin IN NUMBER,
				nMax IN NUMBER,
				curRecords OUT SYS_REFCURSOR
			)
             */


            try
            {
                using (IDb db = ServiceFactory.Instance.GetService<IDb>("sql_server"))
                {
                    // loading definitions
                    XmlDefinitions names = new XmlDefinitions("Names.xml", db.ResourceVersion);
                    XmlDefinitions types = new XmlDefinitions("DbTypes.xml", db.ResourceVersion);
                    XmlDefinitions sql = new XmlDefinitions("Sql.xml", db.ResourceVersion);


                    // executing SQL queries directly

                    // creating data adapter from a string query
                    IDbDataAdapter da = db.CreateDataAdapter(sql["select_1"]); // select * from test_table1
                    DataSet ds = db.FillDataSet(da, "TEST_TABLE1");
                    DisplayDataset(ds);

                    // creating data adapter from a command
                    IDbCommand cmd = db.CreateQueryCommand(sql["select_1"]);
                    da = db.CreateDataAdapter(cmd);
                    ds = db.FillDataSet(da);
                    DisplayDataset(ds);


                    // parameterized query: SELECT * FROM TEST_TABLE1 WHERE ID={0}
                    cmd = db.CreateQueryCommand(sql["select_2"], 
                                                new DbParameterDescriptor("ID", types["ID.Number"], ParameterDirection.Input,0, 100));
                    da = db.CreateDataAdapter(cmd);
                    ds = db.FillDataSet(da);
                    DisplayDataset(ds);

                    // parameterized scalar query: SELECT STATUS FROM TEST_STATUS WHERE ID={0}
                    List<IDbParameterDescriptor> parameters = new List<IDbParameterDescriptor>();
                    parameters.Add(new DbParameterDescriptor("ID", types["ID.Number"], ParameterDirection.Input, 0, 3));
                    cmd = db.CreateQueryCommand(sql["select_3"], parameters.ToArray());
                    object objStatus = db.ExecuteScalar(cmd);
                    Console.WriteLine("SATATUS: {0}", objStatus);



                    // calling stored procedures

                    // using OUT parameter
                    cmd = db.CreateProcedureCommand("SP_GET_CONSTANT",
                                                    new DbParameterDescriptor(names["GET_CONSTANT.CONSTANT"], types["Integer"], ParameterDirection.Output, 4, null));

                    da = db.CreateDataAdapter(cmd);
                    ds = db.FillDataSet(da);

                    DbCommandParamAccessor dbParams = new DbCommandParamAccessor(db, cmd.Parameters);
                    int constant = dbParams.GetInteger(names["GET_CONSTANT.CONSTANT"], 0);

                    Console.WriteLine("CONSTANT: {0}", constant);

                    // returning a cursor
                    // for SQL Server SP there is no need to specify a return cursor parameter
                    // it will be empty in the names[] collection and therefore will be excluded from the parameter list

                    cmd = db.CreateProcedureCommand("SP_GET_STATUSES",
                                                    new DbParameterDescriptor(names["GET_STATUSES.cur_STATUSES"], types["Cursor"], ParameterDirection.Output, 0, null));

                    da = db.CreateDataAdapter(cmd);
                    ds = db.FillDataSet(da, "TEST_STATUS");

                    DisplayDataset(ds);

                    // parsing procdure definition SQL fragment

                    string sProcDefSql = sql["sp_get_status"];
                    IDbProcedureDescriptor procd = db.ParseProcedureDefinitionSql(sProcDefSql);

                    var descriptor = (from desc in procd.ParameterDescriptors
                                     where desc.ParameterName == names["SP_GET_STATUS.ID"]
                                     select desc).Single();
                    descriptor.Value = 1;

                    cmd = db.CreateProcedureCommand(procd);
                    objStatus = db.ExecuteNonQuery(cmd);
                    DbCommandParamAccessor cmdAccessor = new DbCommandParamAccessor(db, cmd.Parameters);
                    Console.WriteLine("SATATUS: {0}", cmdAccessor.GetString(names["SP_GET_STATUS.STATUS"]));
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        static void Test1()
		{
			// PARAMETERIZED STATEMENT

			/* TABLE
			CREATE TABLE [dbo].[test_table1](
				[id] [int] NOT NULL,
				[description] [varchar](max) NOT NULL,
				[string_data] [varchar](50) NULL,
				[numeric_data] [int] NOT NULL,
				[modified_time] [datetime] NULL,
			CONSTRAINT [PK_test_table1] PRIMARY KEY CLUSTERED 
			(
				[id] ASC
			)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
			) ON [PRIMARY]
			
			CREATE TABLE [dbo].[sequence_1](
				[id] [int] IDENTITY(100,1) NOT NULL,
				[flag] [tinyint] NULL
			) ON [PRIMARY]

			CREATE PROCEDURE [dbo].[GetNext] 
			AS
			BEGIN
				SET NOCOUNT ON;
				insert into sequence_1 (flag) values(1);
				delete from sequence_1;
				SELECT @@identity;
			END						
			*/

		    string sDescription = "my new data";
		    string sData = "data: 5498";
		    int nData = 5;
			
			try
			{
				using(IDb db = ServiceFactory.Instance.GetService<IDb>("sql_server"))
				{
					db.Connection.Open();
					//db.BeginTransaction();

					IDbCommand getidcmd = db.CreateProcedureCommand("GetNext");
					int nId = TypedAccessor.GetInteger(db.ExecuteScalar(getidcmd));
					
					string sSqlTemplate = "insert into test_table1 (id, description, string_data, numeric_data, modified_time) values({0}, {1}, {2}, {3}, " + db.CurrentDateFunction + ")";

					List<DbParameterDescriptor> pars = new List<DbParameterDescriptor>();
					pars.Add(new DbParameterDescriptor("id", eDataType.Int, ParameterDirection.Input, 4, nId));
					pars.Add(new DbParameterDescriptor("description", eDataType.UnicodeString, ParameterDirection.Input, 2000, sDescription));
					pars.Add(new DbParameterDescriptor("string_data", eDataType.UnicodeString, ParameterDirection.Input, 50, sData));
					pars.Add(new DbParameterDescriptor("numeric_data", eDataType.Int, ParameterDirection.Input, 4, nData));

					IDbCommand insert_cmd = db.CreateQueryCommand(sSqlTemplate, pars);

					db.ExecuteNonQuery(insert_cmd);
					
					//db.CommitTransaction();
					db.Connection.Close();
				}
				
			}
			catch(DataException ex)
			{
				Console.WriteLine("DATA EXCEPTION: {0}", ex.Message);
			}
			catch(Exception ex)
			{
				Console.WriteLine(ex.Message);
			}

        }

        static void Test2()
        {
            try
            {
                IDb db = ServiceFactory.Instance.GetService<IDb>("sql_server");
                List<IDbParameterDescriptor> pars = new List<IDbParameterDescriptor>();
                pars.Add(new DbParameterDescriptor("min", eDataType.Int, ParameterDirection.Input, 4, 0));
                pars.Add(new DbParameterDescriptor("max", eDataType.Int, ParameterDirection.Input, 4, 5));
                // this is needed for oracle for oracle (SQL Server ignores Recordset):
                pars.Add(new DbParameterDescriptor("records", eDataType.Recordset, ParameterDirection.Output, 0, null));

                IDbCommand cmd = db.CreateProcedureCommand("GetData", pars.ToArray());
                IDbDataAdapter da = db.CreateDataAdapter(cmd);
                DataSet ds = db.FillDataSet(da, "test_table1");

                DataTable t = ds.Tables["test_table1"];

                for (int k = 0; k < t.Rows.Count; k++)
                {
                    DbRow row = new DbRow(t.Rows[k]);

                    int nId = row.IntegerValue["id"];
                    int nData = row.IntegerValue["numeric_data"];
                    string sDescription = row.StringValue["description", nId.ToString()];
                    string sStringData = row.StringValue["string_data", nData.ToString()];
                    DateTime modified = row.DateTimeValue["modified_time", DateTime.MinValue];

                    Console.WriteLine("{0}: {1}, {2}, {3} - modified at: {4}", nId, sDescription, sStringData, nData, modified);
                }
            }
            catch (DataException ex)
            {
                Console.WriteLine("DATA EXCEPTION: {0}", ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }


        #region UTILITIEES

        public static void DisplayDataset(DataSet ds)
        {
            Console.WriteLine("=========================================\nDataSet:\n\n");
            foreach (DataTable dt in ds.Tables)
            {
                DisplayDataTable(dt);
            }
            Console.WriteLine("=========================================");
        }

        public static void DisplayDataTable(DataTable dt)
        {
            Console.WriteLine("---------------\nDataTable: {0}", dt.TableName);

            int nCount = 0;

            foreach (DataColumn col in dt.Columns)
            {
                string sStr = string.Format(" {0} |", col.ColumnName);
                Console.Write(sStr);
                nCount += sStr.Length;
            }
            Console.WriteLine("\n" + new string('-', nCount));

            foreach (DataRow dr in dt.Rows)
            {
                DisplayDataRow(dr, dt.Columns);
            }
            Console.WriteLine("---------------");
        }

        public static void DisplayDataRow(DataRow dr, DataColumnCollection columns)
        {
            foreach (DataColumn col in columns)
            {
                Console.Write(" {0} |", dr[col.ColumnName]);
            }

            Console.WriteLine("");
        }


        #endregion // UTILITIES END
    }
}
